package gov.nhso.prov.acc.apps.acc6;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.isud.core.ISUD;
import com.isud.core.ISUDFactory;
import com.isud.paging.PageBean;

import gov.nhso.prov.acc.beans.Acc6ChequeBean;
import gov.nhso.prov.acc.beans.Acc6ChequeDetailBean;
import gov.nhso.prov.acc.beans.Acc6ExpenseBean;
import gov.nhso.prov.acc.beans.Acc6ExpenseChequeBean;
import gov.nhso.prov.acc.beans.Acc6ExpenseOtherBean;
import gov.nhso.prov.acc.beans.Acc6ExpenseTransferBean;
import gov.nhso.prov.acc.beans.Acc6TransactionBean;
import gov.nhso.prov.acc.core.DBConnection;
import gov.nhso.prov.acc.utils.Utility;

@SuppressWarnings("rawtypes")
public class ACC60307 extends Utility{
	public String url = "/apps/acc6/ACC60307A.jsp";

	public ACC60307(HttpServletRequest request, HttpServletResponse response) {
		this.message(request).clearMsg();
		String job = this.getAttr(request, "job");
		if (job.equals("init")) {
			this.clearLastRequest(request, "lastSearch");
			this.init(request);
		} else if (job.equals("search")) {
			this.setLastRequest(request, "lastSearch");
			this.search(request);
		} else if (job.equals("view")){
			this.view(request);
		} else if (job.equals("back")){
			if (this.getLastRequest(request, "lastSearch")) {
				this.search(request);
			} else {
				this.init(request);
			}
		} else if (job.equals("cancel")) {
			this.cancel(request);
		}
	}
	
	private String init(HttpServletRequest request){
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			
			request.setAttribute("listBudgetYear", this.dataUtil(dao).lsBudgetYear());
			request.setAttribute("job", "search");
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			e.printStackTrace();
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return this.url;
	}
	
	private String search(HttpServletRequest request){
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			PageBean pageBean = new PageBean();
			Acc6ExpenseBean expenseBean = new Acc6ExpenseBean();
			Acc6ChequeBean chequeBean = new Acc6ChequeBean();
			Acc6ExpenseOtherBean expenseOtherBean = new Acc6ExpenseOtherBean();
			String provinceId = this.getAttr(request, "provinceId");
			String budgetYear = this.getAttr(request, "budgetYear");
			String docNo = this.getAttr(request, "docNo");
			String expenseSubject = this.getAttr(request, "expenseSubject");
			String startDate = this.getAttr(request, "startDate");
			String endDate = this.getAttr(request, "endDate");
			String expenseType = this.getAttr(request, "expenseType");
			String condition = "";
			String fileDate = "EXPENSE_DATE";
			
			this.beanUtil().copyRequest2Bean(request, pageBean);
			
			if (expenseType.equals("CL")) {
				fileDate = "CHEQUE_DATE";
			}

			if (startDate != null && !startDate.equals("")){
				startDate = this.dateUtil().changeFormatDate(startDate, "/", "/");
				condition += " AND TRUNC("+fileDate+") >= TO_DATE('"+startDate+"','DD/MM/YYYY') ";
			}
			if (endDate != null && !endDate.equals("")){
				endDate = this.dateUtil().changeFormatDate(endDate, "/", "/");
				condition += " AND TRUNC("+fileDate+") <= TO_DATE('"+endDate+"','DD/MM/YYYY') ";
			}
			
			if (this.user(request).getAreaLevel().equals("2")){	
				condition = " AND PROVINCE_ID IN (SELECT PROVINCE_ID FROM PROVINCE WHERE NHSO_ZONE = '" + this.user(request).getAreaCode()+ "' ) ";
			}
			
			if (expenseType.equals("CL")) {
				chequeBean.setDocNo(docNo);
				chequeBean.setProvinceId(provinceId);
				chequeBean.setActive("Y");
				chequeBean.setChequeStatus("W");
				pageBean = dao.selectPage(chequeBean, " " + condition, pageBean);
			} else if (expenseType.equals("P")) {
				expenseOtherBean.setDocNo(docNo);
				expenseOtherBean.setActive("Y");
				expenseOtherBean.setProvinceId(provinceId);
				expenseOtherBean.setBudgetYear(budgetYear);
				expenseOtherBean.setExpenseStatus("Y");
				pageBean = dao.selectPage(expenseOtherBean, "" + condition, pageBean);
			} else {
				expenseBean.setDocNo(docNo);
				expenseBean.setExpenseSubject(expenseSubject);
				expenseBean.setActive("Y");
				expenseBean.setProvinceId(provinceId);
				expenseBean.setExpenseType(expenseType);
				expenseBean.setBudgetYear(budgetYear);
				expenseBean.setExpenseStatus("W");
				pageBean = dao.selectPage(expenseBean, " " + condition + " AND EXPENSE_TYPE != 'A' ", pageBean);
			}
			
			if (pageBean.getPageData() == null) {
				this.message(request).setMsgCode("I001");
			}

			request.setAttribute("pageBean", pageBean);
			request.setAttribute("provinceId", provinceId);
			request.setAttribute("budgetYear", budgetYear);
			request.setAttribute("startDate", this.getAttr(request, "startDate"));
			request.setAttribute("endDate", this.getAttr(request, "endDate"));
			request.setAttribute("docNo", docNo);
			request.setAttribute("job", "search");
			request.setAttribute("expenseType", expenseType);
			request.setAttribute("expenseStatus", this.properties().statusExpense());
			request.setAttribute("listBudgetYear", this.dataUtil(dao).lsBudgetYear());
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			dao.printMessage();
			e.printStackTrace();
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return this.url;
	}
	
	private String view(HttpServletRequest request){
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			
			String docNo = this.getAttr(request, "docNo");
			String provinceId = this.getAttr(request, "provinceId");
			String budgetYear = this.getAttr(request, "budgetYear");
			String expenseType = this.getAttr(request, "expenseType");
			List listExpense = new ArrayList();
			Acc6ExpenseBean expenseBean = new Acc6ExpenseBean();
			Acc6ChequeBean cb = new Acc6ChequeBean();
			Acc6ChequeDetailBean cdb = new Acc6ChequeDetailBean();
			Acc6ExpenseTransferBean expenseTransferBean = new Acc6ExpenseTransferBean();
			Acc6ExpenseChequeBean expenseChequeBean = new Acc6ExpenseChequeBean();
			Acc6ExpenseOtherBean expenseOtherBean = new Acc6ExpenseOtherBean();
			
			expenseBean = new Acc6ExpenseBean();
			expenseBean.setDocNo(docNo);
			expenseBean.setProvinceId(provinceId);
			expenseBean.setBudgetYear(budgetYear);
			expenseBean.setExpenseType(expenseType);
			expenseBean = (Acc6ExpenseBean) dao.select(expenseBean);
			
			StringBuffer sb = new StringBuffer();
			Map pv = new HashMap();
			if (expenseType.equals("T")) {
				expenseTransferBean.setDocNo(docNo);
				expenseTransferBean.setActive("Y");
				Map<String, String> xcols = new HashMap<String, String>();
				xcols.put("TRANSACTION_TIMESTAMP", "TO_CHAR(TRANSACTION_TIMESTAMP, 'YYYYMMDDHH24MISSFF3')");
				listExpense = dao.selectList(expenseTransferBean, xcols, " ORDER BY TRANSFER_SEQ ASC");
				
				for (int i = 0; i < listExpense.size(); i++) {
					Acc6ExpenseTransferBean bean = (Acc6ExpenseTransferBean) listExpense.get(i);
					sb.delete(0, sb.length());
					sb.append("SELECT CASE WHEN LENGTH(ALLOCATE_NAME) < 21 THEN ALLOCATE_NAME ELSE RPAD(SUBSTR(ALLOCATE_NAME,0,20),23,'.')  END AS ALLOCATE_NAME_SUB,ALLOCATE_NAME ");
					sb.append("FROM "+this.schema+".ACC6_ALLOCATE ");
					sb.append("WHERE ALLOCATE_LEVEL >= 0 AND ALLOCATE_APPLY = 'Y' AND ALLOCATE_CODE='"+bean.getAllocateCode()+"'");
					pv = dao.findDataSql(sb.toString());
					if (pv.get("ALLOCATE_NAME") == null){
						bean.setAllocateName("");
					}else{
						bean.setAllocateName(pv.get("ALLOCATE_NAME").toString());
					}
					if (pv.get("ALLOCATE_NAME_SUB") == null){
						bean.setAllocateSub("");
					}else{
						bean.setAllocateSub(pv.get("ALLOCATE_NAME_SUB").toString());
					}
					sb.delete(0, sb.length());
					pv = new HashMap();
					sb.append("SELECT CASE WHEN LENGTH(VENDER_NAME) < 21 THEN VENDER_NAME ELSE RPAD(SUBSTR(VENDER_NAME,0,20),23,'.')  END AS VENDER_NAME_SUB,VENDER_NAME ");
					sb.append("FROM "+this.schema+".ACC6_VENDER ");
					sb.append("WHERE VENDER_CODE='"+bean.getVenderCode()+"'");
					pv = dao.findDataSql(sb.toString());
					
					if (pv.get("VENDER_NAME") == null){
						bean.setVenderName("");
					}else{
						bean.setVenderName(pv.get("VENDER_NAME").toString());
					}
					if (pv.get("VENDER_NAME_SUB") == null){
						bean.setVenderSub("");
					}else{
						bean.setVenderSub(pv.get("VENDER_NAME_SUB").toString());
					}
				}
			} else if (expenseType.equals("C")) {
				expenseChequeBean.setDocNo(docNo);
				expenseChequeBean.setActive("Y");
				Map<String, String> xcols = new HashMap<String, String>();
				xcols.put("TRANSACTION_TIMESTAMP", "TO_CHAR(TRANSACTION_TIMESTAMP, 'YYYYMMDDHH24MISSFF3')");
				listExpense = dao.selectList(expenseChequeBean, xcols, " ORDER BY CHEQUE_SEQ ASC");

				for (int i = 0; i < listExpense.size(); i++) {
					Acc6ExpenseChequeBean bean = (Acc6ExpenseChequeBean) listExpense.get(i);
					sb.delete(0, sb.length());
					sb.append("SELECT CASE WHEN LENGTH(ALLOCATE_NAME) < 21 THEN ALLOCATE_NAME ELSE RPAD(SUBSTR(ALLOCATE_NAME,0,20),23,'.')  END AS ALLOCATE_NAME_SUB,ALLOCATE_NAME ");
					sb.append("FROM "+this.schema+".ACC6_ALLOCATE ");
					sb.append("WHERE ALLOCATE_LEVEL >= 0 AND ALLOCATE_APPLY = 'Y' AND ALLOCATE_CODE='"+bean.getAllocateCode()+"'");
					pv = dao.findDataSql(sb.toString());
					if (pv.get("ALLOCATE_NAME") == null){
						bean.setAllocateName("");
					}else{
						bean.setAllocateName(pv.get("ALLOCATE_NAME").toString());
					}
					if (pv.get("ALLOCATE_NAME_SUB") == null){
						bean.setAllocateSub("");
					}else{
						bean.setAllocateSub(pv.get("ALLOCATE_NAME_SUB").toString());
					}
					sb.delete(0, sb.length());
					pv = new HashMap();
					sb.append("SELECT CASE WHEN LENGTH(VENDER_NAME) < 21 THEN VENDER_NAME ELSE RPAD(SUBSTR(VENDER_NAME,0,20),23,'.')  END AS VENDER_NAME_SUB,VENDER_NAME ");
					sb.append("FROM "+this.schema+".ACC6_VENDER ");
					sb.append("WHERE VENDER_CODE='"+bean.getVenderCode()+"'");
					pv = dao.findDataSql(sb.toString());
					
					if (pv.get("VENDER_NAME") == null){
						bean.setVenderName("");
					}else{
						bean.setVenderName(pv.get("VENDER_NAME").toString());
					}
					if (pv.get("VENDER_NAME_SUB") == null){
						bean.setVenderSub("");
					}else{
						bean.setVenderSub(pv.get("VENDER_NAME_SUB").toString());
					}
				}
			} else if (expenseType.equals("CL")) {
				cb.setDocNo(docNo);
				cb.setProvinceId(provinceId);
				dao.select(cb);
				cdb.setDocNo(docNo);
				cdb.setActive("Y");
				listExpense = dao.selectList(cdb);
				Map<String, String> xcols = new HashMap<String, String>();
				xcols.put("TRANSACTION_TIMESTAMP", "TO_CHAR(TRANSACTION_TIMESTAMP, 'YYYYMMDDHH24MISSFF3')");
				listExpense = dao.selectList(cdb, xcols," ORDER BY CHEQUE_SEQ ASC");
				for (int i = 0; i < listExpense.size(); i++) {
					Acc6ChequeDetailBean bean = (Acc6ChequeDetailBean) listExpense.get(i);
					sb.delete(0, sb.length());
					sb.append("SELECT CASE WHEN LENGTH(ALLOCATE_NAME) < 21 THEN ALLOCATE_NAME ELSE RPAD(SUBSTR(ALLOCATE_NAME,0,20),23,'.')  END AS ALLOCATE_NAME_SUB,ALLOCATE_NAME ");
					sb.append("FROM "+this.schema+".ACC6_ALLOCATE ");
					sb.append("WHERE ALLOCATE_LEVEL >= 0 AND ALLOCATE_APPLY = 'Y' AND ALLOCATE_CODE='"+bean.getAllocateCode()+"'");
					pv = dao.findDataSql(sb.toString());
					if(pv.get("ALLOCATE_NAME") == null){
						bean.setAllocateName("");
					} else{
						bean.setAllocateName(pv.get("ALLOCATE_NAME").toString());
					}
					if(pv.get("ALLOCATE_NAME_SUB") == null){
						bean.setAllocateSub("");
					} else{
						bean.setAllocateSub(pv.get("ALLOCATE_NAME_SUB").toString());
					}
					sb.delete(0, sb.length());
					pv = new HashMap();
					sb.append("SELECT CASE WHEN LENGTH(VENDER_NAME) < 21 THEN VENDER_NAME ELSE RPAD(SUBSTR(VENDER_NAME,0,20),23,'.')  END AS VENDER_NAME_SUB,VENDER_NAME ");
					sb.append("FROM "+this.schema+".ACC6_VENDER ");
					sb.append("WHERE VENDER_CODE='"+bean.getVenderCode()+"'");
					pv = dao.findDataSql(sb.toString());
					if(pv.get("VENDER_NAME") == null){
						bean.setVenderName("");
					} else{
						bean.setVenderName(pv.get("VENDER_NAME").toString());
					}
					if(pv.get("VENDER_NAME_SUB") == null){
						bean.setVenderSub("");
					} else{
						bean.setVenderSub(pv.get("VENDER_NAME_SUB").toString());
					}
				}
			} else if (expenseType.equals("P")) {
				Map<String, String> xcols = new HashMap<String, String>();
				xcols.put("TRANSACTION_TIMESTAMP", "TO_CHAR(TRANSACTION_TIMESTAMP, 'YYYYMMDDHH24MISSFF3')");
				expenseOtherBean.setDocNo(docNo);
				expenseOtherBean.setBudgetYear(budgetYear);
				expenseOtherBean.setProvinceId(provinceId);
				expenseOtherBean.setActive("Y");
				dao.select(expenseOtherBean , xcols , "");
			}
			request.setAttribute("expenseBean", expenseBean);
			request.setAttribute("cb", cb);
			request.setAttribute("expenseOtherBean", expenseOtherBean);
			request.setAttribute("listExpense", listExpense);
			request.setAttribute("provinceName", this.dataUtil(dao).getProvinceName(provinceId));
			request.setAttribute("docNo", docNo);
			request.setAttribute("expenseType", expenseType);
			request.setAttribute("job", "cancel");
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			e.printStackTrace();
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
				this.url = "/apps/acc6/ACC60307B.jsp";
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return this.url;
	}
	private String cancel(HttpServletRequest request) {
		Connection conn = null;
		ISUD dao = null;
		String docNo = this.getAttr(request, "docNo");
		String budgetYear = this.getAttr(request, "budgetYear");
		String provinceId = this.getAttr(request, "provinceId");
		String expenseType = this.getAttr(request, "expenseType");
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);

			Acc6ExpenseBean aeb = new Acc6ExpenseBean();
			Acc6TransactionBean atb = new Acc6TransactionBean();
			Acc6ChequeBean acb = new Acc6ChequeBean();
			String userId = this.user(request).getUserId();
			if (!expenseType.equals("P")) {
				String[] transactionTimestamp = request.getParameterValues("transactionTimestamp");
				for (int i = 0; i < transactionTimestamp.length ; i++) {
					atb = new Acc6TransactionBean();
					atb.setTransactionTimestamp("TO_TIMESTAMP('" + transactionTimestamp[i] + "', 'YYYYMMDDHH24MISSFF3')");
					dao.delete(atb);
				}
			} else {
				String transactionTimestamp = this.getAttr(request, "transactionTimestamp");
				atb = new Acc6TransactionBean();
				atb.setTransactionTimestamp("TO_TIMESTAMP('" + transactionTimestamp + "', 'YYYYMMDDHH24MISSFF3')");
				dao.delete(atb);
			}
			
			if (expenseType.equals("T")) {
				Acc6ExpenseTransferBean aetb = new Acc6ExpenseTransferBean();
				aetb.setTransactionTimestamp("null");
				aetb.setDocNo(docNo);
				aetb.setUpdatedBy(userId);
				aetb.setUpdatedDate(this.sysDate);
				dao.update(aetb);
			} else if (expenseType.equals("C")) {
				Acc6ExpenseChequeBean aecb = new Acc6ExpenseChequeBean();
				aecb.setTransactionTimestamp("null");
				aecb.setDocNo(docNo);
				aecb.setUpdatedBy(userId);
				aecb.setUpdatedDate(this.sysDate);
				dao.update(aecb);
			}  else if (expenseType.equals("CL")) {
				Acc6ChequeDetailBean acdb = new Acc6ChequeDetailBean();
				acdb.setTransactionTimestamp("null");
				acdb.setDocNo(docNo);
				acdb.setUpdatedBy(userId);
				acdb.setUpdatedDate(this.sysDate);
				dao.update(acdb);
			} else if (expenseType.equals("P")) {
				Acc6ExpenseOtherBean aeob = new Acc6ExpenseOtherBean();
				aeob.setTransactionTimestamp("null");
				aeob.setExpenseStatus("P");
				aeob.setDocNo(docNo);
				aeob.setProvinceId(provinceId);
				aeob.setBudgetYear(budgetYear);
				aeob.setUpdatedBy(userId);
				aeob.setUpdatedDate(this.sysDate);
				dao.update(aeob);
				dao.printMessage();
			}
			
			if (!expenseType.equals("CL")) {
				aeb.setDocNo(docNo);
				aeb.setBudgetYear(budgetYear);
				aeb.setProvinceId(provinceId);
				aeb.setExpenseStatus("P");
				aeb.setUpdatedBy(userId);
				aeb.setUpdatedDate(this.sysDate);
				dao.update(aeb);
			} else {
				acb.setDocNo(docNo);
				acb.setProvinceId(provinceId);
				acb.setChequeStatus("P");
				acb.setUpdatedBy(userId);
				acb.setUpdatedDate(this.sysDate);
				dao.update(acb);
			}
			
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			e.printStackTrace();
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				request.setAttribute("docNo", "");
				request.setAttribute("budgetYear", "");
				request.setAttribute("provinceId", provinceId);
				request.setAttribute("expenseType", expenseType);
				dao = null;
				this.search(request);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return this.url;
	}
}
